"""
Case Type   : 事务-ustore
Case Name   : 索引->事务->Update数据,长度变短->回滚->kill数据库
Create At   : 2022/1/14
Owner       : @kyleze
Description :
    1.创建表
    2.在事务中，Update数据,长度变短
    3.kill数据库
    4.查询数据
    5.环境清理
Expect      :
    1.成功
    2.成功
    3.成功
    4.查询有数据
    5.成功
History     :
"""
import os
import sys
import unittest

from yat.test import macro
from yat.test import Node

from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger


class USTORE(unittest.TestCase):
    def setUp(self):
        self.pri_user = Node('PrimaryDbUser')
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.constant = Constant()
        self.log = Logger()
        self.schema = 'schema_ustore_trans'
        self.table = 't_ustore_trans_0053'
        text = f'---{os.path.basename(__file__)} start---'
        self.log.info(text)
    
    def testunit_1(self):
        text = '----step1:创建表;expect:成功----'
        self.log.info(text)
        q_str = f'''drop table if exists {self.schema}.{self.table} cascade;
            create table {self.schema}.{self.table}(c_id int,c_d_id int not null,
            c_w_id int not null,c_first varchar(16) not null,c_middle char(22),
            c_last varchar(16) not null,c_street_1 varchar(20) not null,c_street_2 varchar(20),c_city varchar(20)
            not null,
            c_state char(22) not null,c_zip char(9) not null,c_phone char(16) not null,
            c_since timestamp,c_credit char(22) not null,c_credit_lim numeric(22,12),c_discount
            numeric(4,4),c_balance numeric(22,12),c_ytd_payment real not null,c_payment_cnt
            number not null,c_delivery_cnt bool not null,c_end date not null,c_data clob,c_clob clob)
            with (storage_type=ustore);
            create unique index {self.schema}.index_ustore_trans_0040_1 ON
            {self.schema}.{self.table}(c_id,c_d_id);
            create index {self.schema}.index_ustore_trans_0040_02 on 
            {self.schema}.{self.table}(c_id);
            create index {self.schema}.index_ustore_trans_0040_03 on 
            {self.schema}.{self.table}(c_city);
            create index {self.schema}.index_ustore_trans_0040_04 on 
            {self.schema}.{self.table}(c_first,c_state);
            create index {self.schema}.index_ustore_trans_0040_05 on 
            {self.schema}.{self.table}(c_id,c_d_id,c_middle);
            create index {self.schema}.index_ustore_trans_0040_06 on 
            {self.schema}.{self.table}(c_id,c_d_id,c_middle,c_street_1);
            insert into {self.schema}.{self.table} select * from {self.schema}.t_ustore_trans_0001;
            '''
        self.log.info(q_str)
        q_res = self.pri_sh.execut_db_sql(q_str)
        self.log.info(q_res)
        self.assertNotIn('ERROR', q_res, '执行失败: ' + text)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, q_res,
                      '执行失败: ' + text)
        self.assertIn(self.constant.INSERT_SUCCESS_MSG, q_res,
                      '执行失败: ' + text)
        self.assertIn(self.constant.CREATE_INDEX_SUCCESS, q_res,
                      '执行失败: ' + text)

        text = '----step2:在事务中，Update数据,长度变短;expect:成功----'
        self.log.info(text)
        q_str = f'''start transaction;
            update {self.schema}.{self.table} set c_data='dddddddddddd',
            c_clob='bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' 
            where mod(c_id,22)=0;
            update {self.schema}.{self.table} set c_first='aaaaaaaaaaaaaaa',
            c_data='qvldetanrbrburbmzqujshoqnggsmnteccipriirdhirwiynpfzcsy
            kxxyscdsfqafhatdokmjogfgslucunvwb',
            c_clob='qvldetanrbrburbmzqujshoqnggsmnteccipriirdhirwiyn' 
            where mod(c_id,22)=1;
            select distinct c_first,count(*) from {self.schema}.{self.table} 
            group by c_first order by c_first;
            rollback;
        '''
        self.log.info(q_str)
        q_res = self.pri_sh.execut_db_sql(q_str)
        self.log.info(q_res)
        self.assertNotIn('ERROR', q_res, '执行失败: ' + text)
        self.assertIn('START TRANSACTION', q_res, '执行失败: ' + text)
        self.assertIn('ROLLBACK', q_res, '执行失败: ' + text)
        self.assertIn('956 rows', q_res, '执行失败: ' + text)
        self.assertIn(self.constant.UPDATE_SUCCESS_MSG, q_res,
                      '执行失败: ' + text)

        text = '----step3:kill-重启数据库;expect:成功----'
        self.log.info(text)
        result = self.pri_sh.restart_db_cluster()
        self.log.info(result)
        status = self.pri_sh.get_db_cluster_status()
        self.assertTrue('Degraded' in status or 'Normal' in status)

        text = '----step4:查询数据;expect:1001----'
        self.log.info(text)
        q_str = f'select distinct c_first,count(*) ' \
                 f'from {self.schema}.{self.table} ' \
                  f'group by c_first order by c_first;'
        self.log.info(q_str)
        q_res = self.pri_sh.execut_db_sql(q_str)
        self.log.info(q_res)
        self.assertNotIn('ERROR', q_res, '执行失败: ' + text)
        self.assertIn('1001 rows', q_res, '执行失败: ' + text)

    def tearDown(self):
        text = '----step5:清理环境;expect:成功----'
        self.log.info(text)
        sql = f'drop table {self.schema}.{self.table} cascade;'
        result = self.pri_sh.execut_db_sql(sql)
        self.log.info(result)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, result,
                      '执行失败: ' + text)
        text = f'---{os.path.basename(__file__)} end---'
        self.log.info(text)
